package org.jeecg.modules.htxincailiao.analytics.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.jeecg.modules.htxincailiao.analytics.domain.vo.*;
import org.jeecg.modules.htxincailiao.analytics.entity.HTNewsDetailVisitAction;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;

/**
 * @Description: ht_news_detail_visit_action
 * @Author: jeecg-boot
 * @Date:   2022-12-10 10:12:02
 * @Version: V1.0
 */
public interface HTNewsDetailVisitActionMapper extends BaseMapper<HTNewsDetailVisitAction> {


//      附：XML转义字符
//
//      &lt;		<		小于号
//      &gt;		>		大于号
//      &amp; 	    & 	    和
//      &apos;	    ' 	    单引号
//      &quot;	    "		双引号

//    <![CDATA[ when min(starttime)<='12:00' and max(endtime)<='12:00' ]]>

    @Select("<script>" +
            "SELECT DATE_FORMAT(visit_time,<![CDATA[ '%Y-%m-%d %H') ]]> AS timeInterval, COUNT(visit_ip) AS pv, COUNT" +
            "(distinct visit_ip) AS uv " +
            "FROM ht_news_detail_visit_action " +
            "<![CDATA[ WHERE visit_time > #{beginTime} AND visit_time < #{endTime} ]]>" +
            "GROUP BY timeInterval" +

            "</script>")
    List<HTNewsDetailVisitInfoVO> queryPVUVData(@Param("beginTime") String beginTime, @Param("endTime") String endTime);


    @Select("<script>" +
            " SELECT *, CONVERT(TA.uv_revisit_total_count/TA.uv_total_count,DECIMAL(15,2)) AS uv_revisit_rate, " +
            " CONVERT(TA.uv_revisit_time_range_count/TA.uv_time_range_count,DECIMAL(15,2)) AS " +
            "uv_time_range_revisit_rate" +
            " FROM" +
            " ( SELECT (" +
            " SELECT COUNT(*) FROM ht_news_detail_visit_action WHERE <![CDATA[  visit_time > #{beginTime} AND visit_time< #{endTime} ]]> ) AS pv_time_range_count,(" +
            " SELECT COUNT(*) FROM (" +
            " SELECT COUNT(visit_ip) FROM ht_news_detail_visit_action WHERE <![CDATA[ visit_time > #{beginTime} AND visit_time < #{endTime} ]]> GROUP BY visit_ip) AS T) AS uv_time_range_count,(" +
            " SELECT COUNT(*) FROM (" +
            " SELECT T.pv_count FROM (" +
            " SELECT COUNT(visit_ip) AS pv_count FROM ht_news_detail_visit_action WHERE <![CDATA[ visit_time > #{beginTime} AND visit_time < #{endTime} ]]> GROUP BY visit_ip) AS T WHERE pv_count> 1) AS TT) AS " +
            "uv_revisit_time_range_count,(" +
            " SELECT COUNT(*) FROM ht_news_detail_visit_action) AS pv_total_count,(" +
            " SELECT COUNT(*) FROM (" +
            " SELECT COUNT(visit_ip) FROM ht_news_detail_visit_action GROUP BY visit_ip) AS T) AS uv_total_count,(" +
            " SELECT COUNT(*) FROM (" +
            " SELECT T.pv_count FROM (" +
            " SELECT COUNT(visit_ip) AS pv_count FROM ht_news_detail_visit_action GROUP BY visit_ip) AS T WHERE " +
            "pv_count > 1) AS TT) AS uv_revisit_total_count" +
            ") AS TA" +
            "</script>")
    HTNewsDetailVisitStatisticsVO queryStatistics(@Param("beginTime") String beginTime,
                                                @Param("endTime") String endTime);

    @Select("<script>" +
            " SELECT " +
            " T.province_id, " +
            " T.province_pv_count, " +
            " T.province_uv_count, " +
            " T.province_total_browse_time, " +
            " CONVERT ( " +
            " T.province_total_browse_time / T.province_pv_count, " +
            " DECIMAL ( 15, 2 )) AS province_average_browse_time, " +
            " a.NAME AS province_name " +
            " FROM " +
            " ( " +
            " SELECT " +
            " province_id, " +
            " COUNT( visit_ip ) AS province_pv_count, " +
            " COUNT( DISTINCT (visit_ip) ) AS province_uv_count, " +
            " SUM( " +
            " TIMESTAMPDIFF( SECOND, visit_time, leave_time )) AS province_total_browse_time " +
            " FROM " +
            " ht_news_detail_visit_action " +
            " WHERE " +
            " <![CDATA[ visit_time > #{beginTime} ]]> " +
            " AND <![CDATA[ visit_time < #{endTime} ]]> " +
            " GROUP BY " +
            " province_id " +
            " ) AS T " +
            " LEFT JOIN ht_area a ON a.id = T.province_id " +
            "</script>")
    List<HTNewsDetailUserVisitDistributionVO> queryUserDistribution(@Param("beginTime") String beginTime,
                                                                  @Param("endTime") String endTime);


    @Select("<script>" +
            "SELECT T.*, CONVERT(T.client_total_browse_time/T.client_pv_count,DECIMAL (15,2)) AS client_average_browse_time " +
            "FROM " +
            " ( " +
            " SELECT client_os, COUNT(visit_ip) AS client_pv_count, COUNT(DISTINCT (visit_ip)) AS client_uv_count,  " +
            " SUM(TIMESTAMPDIFF(SECOND,visit_time,leave_time)) AS client_total_browse_time  " +
            " FROM ht_news_detail_visit_action  " +
            " WHERE visit_time <![CDATA[ > ]]>  #{beginTime}" +
            " AND visit_time <![CDATA[ < ]]>  #{endTime}  " +
            " GROUP BY client_os " +
            " ) AS T" +
            "</script>")
    List<HTNewsDetailClientVisitDistributionVO> queryClientDistribution(@Param("beginTime") String beginTime,
                                                                      @Param("endTime") String endTime);

}
